﻿USE master;
GO

-- delete QLDNData if it exit
IF EXISTS(SELECT * FROM sys.databases WHERE name ='QLDN')
BEGIN
	DROP DATABASE QLDN;
END

-- creat database QLDN
CREATE DATABASE QLDN;

USE QLDN
GO

-- 1 create table VAITRO 
CREATE TABLE VAITRO
	(MaVaiTro VARCHAR(10) PRIMARY KEY NOT NULL,
	TenVaiTro NVARCHAR(50) NOT NULL,
	DienGiai NVARCHAR(MAX) NULL,
	TrangThai BIT NOT NULL
	)
GO

-- 2 create table CHUCNANG 
CREATE TABLE NHOMCHUCNANG
	(MaNhomChucNang VARCHAR(10) PRIMARY KEY NOT NULL,
	TenNhomChucNang NVARCHAR(50) NOT NULL
	)
GO

CREATE TABLE CHUCNANG
	(MaChucNang VARCHAR(10) PRIMARY KEY NOT NULL,
	TenChucNang NVARCHAR(50) NOT NULL,
	MaNhomChucNang VARCHAR(10) FOREIGN KEY(MaNhomChucNang) REFERENCES NHOMCHUCNANG(MaNhomChucNang)
	)
GO

CREATE TABLE CHUCNANGCON
	(MaChucNangCon VARCHAR(10) PRIMARY KEY NOT NULL,
	TenChucNangCon NVARCHAR(50) NOT NULL,
	MaChucNang VARCHAR(10) FOREIGN KEY(MaChucNang) REFERENCES CHUCNANG(MaChucNang)
	)
GO

-- 2 create table QUYENHAN 
CREATE TABLE QUYENHAN
	(MaQuyenHan VARCHAR(10) PRIMARY KEY NOT NULL,
	TenQuyenHan NVARCHAR(50) NOT NULL,
	TrangThai BIT NOT NULL
	)
GO

-- 4 create table NGUOIDUNG 
CREATE TABLE NGUOIDUNG
	(MaNguoiDung VARCHAR(10) PRIMARY KEY NOT NULL,
	MaVaiTro VARCHAR(10) FOREIGN KEY(MaVaiTro) REFERENCES VAITRO(MaVaiTro),
	TenNguoiDung NVARCHAR(50) NOT NULL,
	TaiKhoan VARCHAR(50) NOT NULL,
	MatKhau VARCHAR(50) NOT NULL,
	DienGiai NVARCHAR(MAX) NULL,
	TrangThai BIT NOT NULL,
	NhomNguoiDung NVARCHAR(50) NOT NULL
	)
GO

-- 5 create table VAITRO_CHUCNANG
CREATE TABLE VAITRO_CHUCNANG 
	(MaVaiTro VARCHAR(10) FOREIGN KEY(MaVaiTro) REFERENCES VAITRO(MaVaiTro) NOT NULL,
	MaChucNang VARCHAR(10) FOREIGN KEY(MaChucNang) REFERENCES CHUCNANG(MaChucNang) NOT NULL,
	CONSTRAINT PK_VAITRO_CHUCNANG PRIMARY KEY CLUSTERED (MaVaiTro, MaChucNang)
	)
GO

-- 6 create table CHUCNANG_QUYENHAN
CREATE TABLE CHUCNANG_QUYENHAN
	(MaChucNang VARCHAR(10) FOREIGN KEY(MaChucNang) REFERENCES CHUCNANG(MaChucNang) NOT NULL,
	MaQuyenHan VARCHAR(10) FOREIGN KEY(MaQuyenHan) REFERENCES QUYENHAN(MaQuyenHan) NOT NULL,
	CONSTRAINT PK_CHUCNANG_QUYENHAN PRIMARY KEY CLUSTERED (MaChucNang, MaQuyenHan)
	)
GO

-- Phan Thai ---------------------------------------------------------------- 

-- 7 Create table KHU VUC
CREATE TABLE KHUVUC
	(MaKhuVuc VARCHAR(10) PRIMARY KEY NOT NULL,
	TenKhuVuc NVARCHAR(255) NOT NULL,
	GhiChu NVARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 8 Create table LOAIHANGHOA
CREATE TABLE LOAIHANGHOA
	(
	MaLoaiHangHoa VARCHAR(10) PRIMARY KEY NOT NULL,
	TenLoaiHangHoa NVARCHAR(255) NOT NULL,
	GhiChu NVARCHAR(MAX) NULL
	)
GO

-- 9 Create table DONVITINH
CREATE TABLE DONVITINH
	(MaDonViTinh VARCHAR(10) PRIMARY KEY NOT NULL,
	TenDonViTinh NVARCHAR(255) NOT NULL,
	MoTa NVARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 10 Create table NHACUNGCAP
CREATE TABLE NHACUNGCAP
	(MaNhaCungCap VARCHAR(10) PRIMARY KEY NOT NULL,
	TenNhaCungCap NVARCHAR(255) NOT NULL,
	DiaChi NVARCHAR(MAX) NULL,
	DienThoai VARCHAR(50) NULL,
	Fax VARCHAR(50) NULL,
	Mail VARCHAR(50) NULL,
	DiDong VARCHAR(50) NULL,
	WebSite VARCHAR(MAX) NULL,
	NguoiLienHe NVARCHAR(255) NULL,
	ChucVu NVARCHAR(255) NULL,
	NickFaceBook VARCHAR(255) NULL,
	NickSkype VARCHAR(255) NULL,
	TaiKhoan VARCHAR(50) NULL,
	NganHang NVARCHAR(50) NULL,
	GioiHanNo MONEY NULL,
	ChietKhau FLOAT NULL,
	NoHienTai MONEY NULL,
	MoTa NVARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 11 Create table HANGHOA
CREATE TABLE HANGHOA
	(MaHangHoa VARCHAR(10) PRIMARY KEY NOT NULL,
	TenHangHoa NVARCHAR(255) NOT NULL,
	MaLoaiHangHoa VARCHAR(10) FOREIGN KEY(MaLoaiHangHoa) REFERENCES LOAIHANGHOA(MaLoaiHangHoa) NOT NULL,
	MaNhaCungCap VARCHAR(10) FOREIGN KEY(MaNhaCungCap) REFERENCES NHACUNGCAP(MaNhaCungCap) NOT NULL,
	XuatXu NVARCHAR(255) NULL,
	MaVach VARCHAR(50) NULL,
	MaDonViTinh VARCHAR(10) FOREIGN KEY(MaDonViTinh) REFERENCES DONVITINH(MaDonViTinh) NOT NULL,
	ThoiGianBaoHanh int NULL,
	MoTa NVARCHAR(MAX) NULL,
	Anh IMAGE NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- Phần Lịch Trọng --------------------------------------------------------

-- 12 Create table LOAIKHACHHANG
CREATE TABLE LOAIKHACHHANG
	(MaLoaiKhachHang VARCHAR(10) PRIMARY KEY NOT NULL,
	TenLoaiKhachHang NVARCHAR(50) NOT NULL,
	DienGiai NVARCHAR(MAX) NULL
	)
GO

-- 13 Create table KHACHHANG
CREATE TABLE KHACHHANG
	(MaKhachHang VARCHAR(10) PRIMARY KEY NOT NULL,
	TenKhachHang NVARCHAR(100) NOT NULL,
	MaLoaiKhachHang	VARCHAR(10) FOREIGN KEY(MaLoaiKhachHang) REFERENCES LOAIKHACHHANG(MaLoaiKhachHang) NOT NULL,
	MaKhuVuc VARCHAR(10) FOREIGN KEY(MaKhuVuc) REFERENCES KHUVUC(MaKhuVuc) NOT NULL,
	NgaySinh DATE NULL,
	DiaChi NVARCHAR(MAX) NULL,
	DienThoai VARCHAR(20),
	Email VARCHAR(100)
	)
GO

-- 14 Create table TRANGTHAI
CREATE TABLE TRANGTHAI
	(MaTrangThai VARCHAR(10) PRIMARY KEY NOT NULL,
	TenTrangThai NVARCHAR(100) NOT NULL
	)
GO

-- 15 Create Table LOAIDICHVU
CREATE TABLE LOAIDICHVU
	(MaLoaiDichVu VARCHAR(10) PRIMARY KEY NOT NULL,
	TenLoaiDichVu NVARCHAR(100) NOT NULL,
	DienGiai NVARCHAR(MAX) NULL
	)
GO

-- 16 Create table DICHVU
CREATE TABLE DICHVU
	(MaDichVu VARCHAR(10) PRIMARY KEY NOT NULL,
	MaLoaiDichVu VARCHAR(10) FOREIGN KEY(MaLoaiDichVu) REFERENCES LOAIDICHVU(MaLoaiDichVu) NOT NULL,
	TenDichVu NVARCHAR(100) NOT NULL,
	TienCong MONEY NOT NULL
	)
GO

-- 17 Create table XE
CREATE TABLE XE
	(BienSoXe VARCHAR(10) PRIMARY KEY NOT NULL,
	SoSuon NVARCHAR(50) NOT NULL,
	LoaiXe NVARCHAR(100) NOT NULL,
	HangSanXuat NVARCHAR(100) NOT NULL,
	DongXe NVARCHAR(100)
	)
GO

-- 18 Create Table BAOGIA
CREATE TABLE BAOGIA
	(MaBaoGia VARCHAR(10) PRIMARY KEY NOT NULL,
	NgayLap DATE NOT NULL,
	MaKhachHang VARCHAR(10) FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) NOT NULL,
	BienSoXe VARCHAR(10) FOREIGN KEY(BienSoXe) REFERENCES XE(BienSoXe) NOT NULL,
	MaNguoiDung VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	TongTien MONEY NOT NULL
	)
GO

-- 19 Create table CHITIETBAOGIA
CREATE TABLE CHITIETBAOGIA
	(MaChiTietBaoGia VARCHAR(10) PRIMARY KEY NOT NULL,
	MaBaoGia VARCHAR(10) FOREIGN KEY(MaBaoGia) REFERENCES BAOGIA(MaBaoGia) NOT NULL,
	MaDichVu VARCHAR(10) FOREIGN KEY(MaDichVu) REFERENCES DICHVU(MaDichVu) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN kEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	SoLuong INT NOT NULL,
	DonGia MONEY NOT NULL,
	ThanhTien MONEY NOT NULL
	)
GO

--phần Thái-------------------------------------------------------------

-- 20 Create table KHO
CREATE TABLE KHO
	(MaKho VARCHAR(10) PRIMARY KEY NOT NULL,
	TenKho NVARCHAR(255) NOT NULL,
	NguoiLienHe NVARCHAR(255) NULL,
	DiaChi NVARCHAR(255) NULL,
	Email VARCHAR(255) NULL,
	DienThoai VARCHAR(20) NULL,
	Fax VARCHAR(20) NULL,
	DiDong VARCHAR(20) NULL,
	NguoiQuanLy NVARCHAR(255) NULL,
	MoTa NVARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 21 Create table TONKHO
CREATE TABLE TONKHO
	(MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	TonKhoToiDa INT NULL,
	TonKhoToiThieu INT NULL,
	TonKhoHienTai INT NULL,
	CONSTRAINT PK_TONKHO PRIMARY KEY CLUSTERED (MaKho, MaHangHoa)
	)
GO

-- 22 Create table PHIEUXUATKHO
CREATE TABLE PHIEUXUATKHO
	(MaPhieuXuatKho VARCHAR(10) PRIMARY KEY NOT NULL,
	NgayXuatKho DATE NOT NULL,
	MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	MaKhachHang VARCHAR(10) FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) NOT NULL,
	NguoiLienHe	NVARCHAR(20) NOT NULL,
	MaNguoiDung VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	LyDo NVARCHAR(255) NULL,
	ChietKhau FLOAT NULL,
	ThanhTien MONEY NULL,
	MoTa NVARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 23 create table CHITIETPHIEUXUATKHO
CREATE TABLE CHITIETPHIEUXUATKHO
	(MaPhieuXuatKho VARCHAR(10) FOREIGN KEY(MaPhieuXuatKho) REFERENCES PHIEUXUATKHO(MaPhieuXuatKho) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	Thue FLOAT NULL,
	SoLuong INT NOT NULL,
	DonGia MONEY NOT NULL,
	ThanhTien MONEY NOT NULL,
	MoTa NVARCHAR(MAX) NULL,
	CONSTRAINT PK_CHITIETPHIEUXUATKHO PRIMARY KEY CLUSTERED (MaPhieuXuatKho, MaHangHoa)
	)
GO

-- 24 Create table PHIEUNHAPKHO
CREATE TABLE PHIEUNHAPKHO
	(MaPhieuNhapKho VARCHAR(10) PRIMARY KEY NOT NULL,
	NgayNhapKho	DATE NOT NULL,
	MaNguoiDung VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	MaHopDong VARCHAR(10) NOT NULL,
	MaNhaCungCap VARCHAR(10) FOREIGN KEY(MaNhaCungCap) REFERENCES NHACUNGCAP(MaNhaCungCap) NOT NULL,
	MaKhachHang VARCHAR(10) FOREIGN KEY(MaKhachHang) REFERENCES KHACHHANG(MaKhachHang) NOT NULL,
	NguoiDaiDien NVARCHAR(MAX) NULL,
	LyDo NVARCHAR(MAX) NULL,
	ThanhTien MONEY NULL,
	ChietKhau FLOAT NOT NULL,
	MoTa NVARCHAR(MAX) NULL,
	ConQuanLy BIT NOT NULL
	)
GO

-- 25 Create table CHITIETPHIEUNHAPKHO
CREATE TABLE CHITIETPHIEUNHAPKHO
	(MaPhieuNhapKho VARCHAR(10) FOREIGN KEY(MaPhieuNhapKho) REFERENCES PHIEUNHAPKHO(MaPhieuNhapKho) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	Thue FLOAT NULL,
	SoLuong INT NOT NULL,
	DonGia MONEY NOT NULL,
	ThanhTien MONEY NOT NULL,
	CONSTRAINT PK_CHITIETPHIEUNHAPKHO PRIMARY KEY CLUSTERED (MaPhieuNhapKho, MaHangHoa)
	)
GO

-- 26 Create table PHIEUKIEMKE
CREATE TABLE PHIEUKIEMKE
	(MaPhieuKiemKe VARCHAR(10) PRIMARY KEY NOT NULL,
	NgayKiemKe DATE NOT NULL,
	MaNguoiDung	VARCHAR(10) FOREIGN KEY(MaNguoiDung) REFERENCES NGUOIDUNG(MaNguoiDung) NOT NULL,
	MaKho VARCHAR(10) FOREIGN KEY(MaKho) REFERENCES KHO(MaKho) NOT NULL,
	LyDo NVARCHAR(MAX) NULL,
	MoTa NVARCHAR(MAX) NULL,
	KetQua NVARCHAR(MAX) NOT NULL
	)
GO

-- 27 create table CHITIETPHIEUKIEMKE
CREATE TABLE CHITIETPHIEUKIEMKE
	(MaPhieuKiemKe VARCHAR(10) FOREIGN KEY(MaPhieuKiemKe) REFERENCES PHIEUKIEMKE(MaPhieuKiemKe) NOT NULL,
	MaHangHoa VARCHAR(10) FOREIGN KEY(MaHangHoa) REFERENCES HANGHOA(MaHangHoa) NOT NULL,
	SoLuongTon INT NULL,
	SoLuongKiemKe INT NULL,
	SoLuongChenhLech INT NULL,
	MoTa NVARCHAR(MAX) NULL,
	CONSTRAINT PK_CHITIETPHIEUKIEMKE PRIMARY KEY CLUSTERED (MaPhieuKiemKe, MaHangHoa)
	)
GO